﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace xuyiying
{
    class SqlOperate
    {
        private const string SqlConnectionString = "Server=302-7\\SQLEXPRESS;Database=StuInfo;Trusted_Connection=SSPI";

        public SqlOperate()
        {

        }
        public void Open(SqlConnection cnn)
        {
            if (cnn.State == ConnectionState.Closed)
            {
                cnn.Open();
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="cnn">连接</param>
        public void Close(SqlConnection cnn)
        {
            if (cnn != null)
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
                cnn.Dispose();
            }
        }
        /// <summary>
        /// 查询（OleDbDataReader）
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <returns>查询结果</returns>
        public SqlDataReader GetDR(string strSql)
        {
            SqlConnection cnn = new SqlConnection(SqlConnectionString);

            SqlCommand cmd;
            try
            {
                Open(cnn);
                cmd = new SqlCommand(strSql, cnn);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {

            }
        }

        /// <summary>
        /// 查询（DataSet）
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <returns>查询结果</returns>
        public DataSet GetDS(string strSql)
        {
            SqlConnection cnn = new SqlConnection(SqlConnectionString);
            SqlDataAdapter sda = new SqlDataAdapter();
            try
            {
                Open(cnn);
                sda = new SqlDataAdapter(strSql, cnn);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                return ds;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                sda.Dispose();
                Close(cnn);
            }
        }

        public int RunSql(string strSql)
        {
            SqlConnection cnn = new SqlConnection(SqlConnectionString);
            SqlCommand cmd;
            try
            {
                Open(cnn);
                cmd = new SqlCommand(strSql, cnn);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {

                Close(cnn);
            }
        }
    }
}

